using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;

namespace Os.Infra
{
	public class MysqlData
	{
		public static readonly string CONN_STRING = ConfigurationManager.ConnectionStrings["ConnectionMySql"].ConnectionString;
		public static MySqlCommand ExecutarComando(MySqlTransaction trans, CommandType cmdType, string cmdText, List<MySqlParameter> cmdParms)
		{
            MySqlCommand mySqlCommand = new MySqlCommand();
            using (MySqlConnection mySqlConnection = new MySqlConnection(MysqlData.CONN_STRING))
            {
                try
                {
                    MysqlData.PrepararComando(mySqlCommand, mySqlConnection, trans, cmdType, cmdText, cmdParms);
                    mySqlCommand.ExecuteNonQuery();
                }
                catch (FormatException ex)
                {
                    throw ex;
                }
                catch (Exception ex)
                {
                    mySqlConnection.Close();
                    mySqlConnection.Dispose();
                    throw ex;
                }
            }
            return mySqlCommand;
		}
		public static MySqlCommand ExecutarComando(CommandType cmdType, string cmdText, List<MySqlParameter> cmdParms)
		{
			MySqlCommand mySqlCommand = new MySqlCommand();
            using (MySqlConnection mySqlConnection = new MySqlConnection(MysqlData.CONN_STRING))
			{
				try
				{
                    MysqlData.PrepararComando(mySqlCommand, mySqlConnection, null, cmdType, cmdText, cmdParms);
					mySqlCommand.ExecuteNonQuery();
				}
				catch (FormatException ex)
				{
					throw ex;
				}
				catch (Exception ex)
				{
					mySqlConnection.Close();
					mySqlConnection.Dispose();
					throw ex;
				}
			}
			return mySqlCommand;
		}
		public static MySqlDataReader ExecutarLeitura(CommandType cmdType, string cmdText, List<MySqlParameter> cmdParms)
		{
			MySqlDataReader result = null;
            MySqlConnection mySqlConnection = new MySqlConnection(MysqlData.CONN_STRING);
			try
			{
				MySqlCommand mySqlCommand = new MySqlCommand();
                MysqlData.PrepararComando(mySqlCommand, mySqlConnection, null, cmdType, cmdText, cmdParms);
				result = mySqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch (MySqlException ex)
			{
                MySqlConnection.ClearAllPools();
				throw ex;
			}
			catch (Exception ex)
			{
                MySqlConnection.ClearAllPools();
				mySqlConnection.Close();
				mySqlConnection.Dispose();
				throw ex;
			}
			return result;
		}
		public static MySqlDataReader ExecutarLeitura(MySqlTransaction trans, CommandType cmdType, string cmdText, List<MySqlParameter> cmdParms)
		{
			MySqlCommand mySqlCommand = new MySqlCommand();
            MysqlData.PrepararComando(mySqlCommand, trans.Connection, trans, cmdType, cmdText, cmdParms);
			return mySqlCommand.ExecuteReader();
		}
		public static DataSet ExecutarLeituraDataSet(CommandType cmdType, string cmdText, List<MySqlParameter> cmdParms)
		{
			DataSet dataSet = new DataSet();
            using (MySqlConnection mySqlConnection = new MySqlConnection(MysqlData.CONN_STRING))
			{
				try
				{
					MySqlCommand mySqlCommand = new MySqlCommand();
                    MysqlData.PrepararComando(mySqlCommand, mySqlConnection, null, cmdType, cmdText, cmdParms);
					MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(mySqlCommand);
					mySqlDataAdapter.Fill(dataSet);
					mySqlCommand.Parameters.Clear();
				}
				catch (Exception ex)
				{
					mySqlConnection.Close();
					mySqlConnection.Dispose();
					throw ex;
				}
			}
			return dataSet;
		}
		public static DataSet ExecutarLeituraDataSet(MySqlTransaction trans, CommandType cmdType, string cmdText, List<MySqlParameter> cmdParms)
		{
			DataSet dataSet = new DataSet();
			MySqlCommand mySqlCommand = new MySqlCommand();
            MysqlData.PrepararComando(mySqlCommand, trans.Connection, trans, cmdType, cmdText, cmdParms);
			MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(mySqlCommand);
			mySqlDataAdapter.Fill(dataSet);
			mySqlCommand.Parameters.Clear();
			return dataSet;
		}
		public static object ExecutarValor(CommandType cmdType, string cmdText, List<MySqlParameter> cmdParms)
		{
			object result = new object();
            using (MySqlConnection mySqlConnection = new MySqlConnection(MysqlData.CONN_STRING))
			{
				try
				{
					MySqlCommand mySqlCommand = new MySqlCommand();
                    MysqlData.PrepararComando(mySqlCommand, mySqlConnection, null, cmdType, cmdText, cmdParms);
					result = mySqlCommand.ExecuteScalar();
					mySqlCommand.Parameters.Clear();
				}
				catch (Exception ex)
				{
					mySqlConnection.Close();
					mySqlConnection.Dispose();
					throw ex;
				}
				finally
				{
					mySqlConnection.Close();
					mySqlConnection.Dispose();
				}
			}
			return result;
		}
		public static object ExecutarValor(MySqlTransaction trans, CommandType cmdType, string cmdText, List<MySqlParameter> cmdParms)
		{
			MySqlCommand mySqlCommand = new MySqlCommand();
            MysqlData.PrepararComando(mySqlCommand, trans.Connection, trans, cmdType, cmdText, cmdParms);
			object result = mySqlCommand.ExecuteScalar();
			mySqlCommand.Parameters.Clear();
			return result;
		}
		private static void PrepararComando(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, CommandType cmdType, string cmdText, List<MySqlParameter> cmdParms)
		{
			if (conn.State != ConnectionState.Open)
			{
                conn.Close();
				conn.Open();
			}
			cmd.Connection = conn;
			cmd.CommandText = cmdText;
			if (trans != null)
			{
				cmd.Transaction = trans;
			}
			cmd.CommandType = cmdType;
			if (cmdParms != null)
			{
				foreach (MySqlParameter current in cmdParms)
				{
					if (cmd.Parameters.Contains(current))
					{
						cmd.Parameters[current.ParameterName] = current;
					}
					else
					{
						cmd.Parameters.Add(current);
					}
				}
			}
		}
	}
}
